package MaintMenu.VenPricingUpdTBLN;

import MaintMenu.PoAnalysis.CostCal;

import javax.swing.*;
import java.sql.*;
import java.text.NumberFormat;

/**
 * Created with IntelliJ IDEA.
 * User: Jason
 * Date: 7/30/13
 * Time: 5:01 PM
 * To change this template use File | Settings | File Templates.
 */
public class FindItemByVendorSheetPL {


    public FindItemByVendorSheetPL(){

    }

    public FindItemByVendorSheetPL(String ven, String partN, int numOfSheet){
//        System.out.println("Start "+cat+" Update");
        NumberFormat df = NumberFormat.getCurrencyInstance();
        String vendor=ven;
        String tbl="";
        String tblPost="";
        String pfield="";
        String findPart="'"+partN+"'";
        String searchResult="";
        String vencostfield="";
        double venCost=0.00D;
        String cuftField="";
        double cuft=0.00d;
        double cif=0.00D;
        String pkgfield="";
        String reffield="";
        String ref;
        int pkg=0;
        int sheetNum=0;


//        System.out.println("value passed over are "+ven +" "+ partN +" "+ numOfSheet);
//        int numOfSheet=0;


        //0 = PL ; 1=part#
//        if (updateBy==0) {
//            pfield="partlink";
//
//        }if (updateBy==1) {
//            pfield="part#";
//        }
        if (!partN.equals("")){

        //Categories
        if (ven=="TW") {
            tbl="_costTygTw";
            pfield="partslink";
            vencostfield="price";
            cuftField="CU/FT";
            pkgfield="STD QTY";
            reffield="CAPA";


            numOfSheet= numOfSheet+1;

        }if (ven=="TX"){
            tbl="_costTygTx";
            pfield="[PartsLink #]";
            vencostfield="DNK $";
            cuftField="";
            pkgfield="";
            reffield="CAPA";

            numOfSheet= numOfSheet+1;

            //System.out.println("start gn");
        }if (ven=="GN"){
            tbl="_COSTGENERA";
            pfield="[PartsLink #]";
            vencostfield="Net $";
            cuftField="";
            pkgfield="";
            reffield="Customer Part #";
            numOfSheet= numOfSheet+1;

            //System.out.println("start dp");
        }if (ven=="DP"){
            tbl="_COSTDEPO";
            pfield="partslink";
            vencostfield="Unit Price";
            cuftField="";
            pkgfield="";
            reffield="CAPA";
            numOfSheet= numOfSheet+1;

        }if (ven=="AP"){
            tbl="_costAP";
            pfield="prtlnk";
            vencostfield="UPX";
            cuftField="CuftPer";
            pkgfield="STD P/K";
            reffield="CAPA";
            numOfSheet= numOfSheet+1;

        }if (ven=="YCC"){
            tbl="_costYCC";
            pfield="partlink";
            vencostfield="price";
            cuftField="CUFT";
            pkgfield="PCS/CTN";
            reffield="CAPA";
            numOfSheet= numOfSheet+1;



//            tblPost="LI";
//            ven="LT";
//        }if (ven=="LTBK"){
//            tbl="tblltn_TEMPOUTBK2";
////            tblPost="BK";
////            cat="LT";
        }
//        //System.out.println(ven+" "+tbl +" "+pfield+" "+numOfSheet);





//        System.out.println(notice);
        try {
//            double cuftCost=2.5D;               //**** CUFT Cost $2.5 per cuft

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = "dbLI";
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection con = DriverManager.getConnection(dbURL, "", "");
            Statement sta = con.createStatement();

//            System.out.println("1");
            try {

                for (int x=1 ; x<numOfSheet ;x++){

//                System.out.println("pfield :"+pfield);
//                getVenField();
//                CostCal bestVen = new CostCal();
//                Statement stupd = con.createStatement();

                String selecSta ="SELECT * FROM "+tbl+x+" where "+pfield+" = "+findPart;
                    sheetNum=x;

//                String selecSta ="SELECT * FROM "+tbl+"";
//                System.out.println("Statement :"+selecSta);
                sta.execute(selecSta);

                ResultSet resultSet = sta.getResultSet();

                int rowupd =0;
//                System.out.println("2");
//                System.out.println(resultSet.getRow());
//                if (resultSet==null){
//                        System.out.println("No Match found");
//                    }

                if (resultSet != null)  {
//                    if (flash_CursorB==1){
//                        System.out.print("\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b");
//                        flash_CursorA=0;
//                        flash_CursorB=1;
//                    }
                    while (resultSet.next()) {
                        if (vendor=="TW" || vendor=="AP" || vendor=="YCC"){
                            venCost=resultSet.getDouble(vencostfield);
                            pkg=resultSet.getInt(pkgfield);
                            cuft= resultSet.getDouble(cuftField);
                            cif = venCost+(cuft*2.5);

                        }else{

                            String venCostString=resultSet.getString(vencostfield);
                            venCost=Double.parseDouble(venCostString);
                            cif=venCost;
                        }


                        String Oref = resultSet.getString(reffield);
//                        System.out.println("REF is:"+Oref);
                        if (Oref==null){
                            ref="Non-CAPA";


                        }else if (Oref.equals("0")){
                            ref="Non-CAPA";
                        }else if (Oref.equals("Y")){
                            ref="CAPA";
                        }else if (Oref.equals("1")){
                            ref="CAPA";
                        }else if (Oref.length()!=0){
                            ref=Oref;
                        }else {ref=null; }
                        rowupd++;
//                    if (resultSet.next()) {
//                        if (!resultSet.next())
//                            break;

                        if (vendor=="TW" || vendor=="AP" || vendor=="YCC"){
//                            System.out.println("Part Number "+partN+" Found in sheet :"+tbl+x +" \nCost(FOB) :"+df.format(venCost)+"\n[PKG:"+pkg+" CUFT:"+cuft+" * 2.5 = "+(cuft*2.5)+"]\nCost (CIF) :"+df.format(cif)+"\n" +
//                                    "Ref:"+ref+"\n");
                            JOptionPane.showMessageDialog(null,"Part Number "+partN+" Found in sheet :"+tbl+x +" \nCost(FOB) :"+df.format(venCost)+"\n[PKG:"+pkg+" CUFT:"+cuft+" * 2.5 = "+(cuft*2.5)+"]\nCost (CIF) :"+df.format(cif)+"\n" +
                                    "Ref :"+ref+"   ");

                        }else{
//                            System.out.println("Part Number "+partN+" Found in sheet :"+tbl+x +" \nCost(FOB) :"+df.format(venCost)+"\nCost (CIF) :"+df.format(cif)+"\n" +
//                                    "Ref:"+ref+"\n");
                            JOptionPane.showMessageDialog(null,"Part Number "+partN+" Found in sheet :"+tbl+x +" \nCost(FOB) :"+df.format(venCost)+"\nCost (CIF) :"+df.format(cif)+"\n" +
                                    "Ref :"+ref+"   ");
                        }

//                            System.out.println("Part Number "+partN+" Found in sheet :"+tbl+x +" Cost :"+venCost);

                    }
                }
            }
            }catch (SQLException er) {
//                System.out.println(er);
//                System.out.println("Vendor file not found");
            }

        }catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        }else{
            JOptionPane.showMessageDialog(null,"Can't Find PL# in :"+tbl+sheetNum);
        }
    }
    //this method will change the field name of table based on the cat
    public void getVenField(){
//        if (cat=="BC" ||cat=="HF" || cat=="OT" || cat=="CL" || cat=="MR"|| cat=="LT") {
             ty_p="tyg_tw_p";
             ty_c="tyg_tw_c";
             tx_p="tyg_tx_p";
             tx_c="";
             gd_p="gd_p";
             gd_c="gd_c";
             pt_p="protec_p";
             pt_c="protec_c";
             gn_p="gn_p";
             gn_c="gn_c";
             dp_p="depo_p";
             dp_c="depo_c";
             ks_p="ks_p";
             ks_c="ks_c";
             kv_p="kv_p";
             kv_c="kv_c";
             ee_p="ee_p";
             ee_c="ee_c";
//        }

//        }if (cat=="HF"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";


//        }if (cat=="OT"){
//            ty_p="tyg_tw_p";
//            ty_c="tyg_tw_c";
//            tx_p="tyg_tx_p";
//            tx_c="";
//            gd_p="gd_p";
//            gd_c="gd_c";
//            pt_p="protec_p";
//            pt_c="protec_c";
//            gn_p="gn_p";           //not use
//            gn_c="gn_c";           //not use
//            dp_p="depo_p";         //not use
//            dp_c="depo_c";          //not use
//            ks_p="ks_p";            //not use
//            ks_c="ks_c";            //not use
//            kv_p="kv_p";            //not use
//            kv_c="kv_c";           //not use

//        }if (cat=="HF"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";

//        }if (cat=="CL"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";

//        }if (cat=="MR"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";
//        }
    }

    public void resetVariables(){

//        supplier=" ";



        flash_CursorA=0;
        flash_CursorB=0;

        tw_shipCost = 0.00D;
        tw = 0.00D;

        tx = 0.00D;

        gd_shipCost = 0.00D;
        gd = 0.00D;

        pt_shipCost = 0.00D;
        pt = 0.00D;

        gn = 0.00D;

        dp = 0.00D;

        ks = 0.00D;

        kv = 0.00D;

        ee = 0.00D;
    }

    //Variables

    String supplier=" ";


    int flash_CursorA=0;
    int flash_CursorB=0;

    double tw_shipCost = 0.00D;
    double tw = 0.00D;

    double tx = 0.00D;

    double gd_shipCost = 0.00D;
    double gd = 0.00D;

    double pt_shipCost = 0.00D;
    double pt = 0.00D;

    double gn = 0.00D;

    double dp = 0.00D;

    double ks = 0.00D;

    double kv = 0.00D;

    double ee = 0.00D;

    //  Variables maps to UA Database tbl??n_tempout2

    String ty_p="";
    String ty_c="";

    String tx_p="";
    String tx_c="";

    String gd_p="";
    String gd_c="";

    String pt_p="";
    String pt_c="";

    String gn_p="";
    String gn_c="";

    String dp_p="";
    String dp_c="";

    String ks_p="";
    String ks_c="";

    String kv_p="";
    String kv_c="";

    String ee_p="";
    String ee_c="";
}
